package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.stereotype.Repository;

import co.gov.medellin.ssti.sub.model.dao.AreaDAO;
import co.gov.medellin.ssti.sub.model.vo.AreaVO;

@Repository
public class AreaDAOImpl2 extends NamedParameterJdbcDaoSupport implements
		AreaDAO {

	public List<AreaVO> findAreas(String strNombreArea) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();
		List<AreaVO> areas;

		StringBuffer query = new StringBuffer(
				"SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
						+ " FROM TCS_BEN_AREA WHERE AREA_CODIGO IS NULL ");

		if ((Object) strNombreArea != null) {
			query.append(" AND NOMBRE LIKE UPPER(:nombre) ");
			namedParameters.put("nombre", "%" + strNombreArea + "%");
		}
		query.append(" ORDER BY NOMBRE");

		areas = getNamedParameterJdbcTemplate().query(query.toString(),
				namedParameters, new RowMapper<AreaVO>() {
					public AreaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AreaVO areaVO = new AreaVO();
						areaVO.setCode(rs.getLong("CODIGO"));
						areaVO.setCodearea(rs.getString("AREA_CODIGO"));
						areaVO.setDescripcion(rs.getString("DESCRIPCION"));
						areaVO.setNombre(rs.getString("NOMBRE"));
						return areaVO;
					}
				});
		return areas;
	}

	public List<AreaVO> findAllAreas() {
		List<AreaVO> areas;

		String query = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
				+ " FROM TCS_BEN_AREA "
				+ " WHERE AREA_CODIGO IS NULL "
				+ " ORDER BY NOMBRE";

		areas = getNamedParameterJdbcTemplate().query(query.toString(),
				new HashMap<String, Object>(), new RowMapper<AreaVO>() {
					public AreaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AreaVO areaVO = new AreaVO();
						areaVO.setCode(rs.getLong("CODIGO"));
						areaVO.setCodearea(rs.getString("AREA_CODIGO"));
						areaVO.setDescripcion(rs.getString("DESCRIPCION"));
						areaVO.setNombre(rs.getString("NOMBRE"));
						return areaVO;
					}
				});
		return areas;
	}

	public Collection<AreaVO> findArea(String strCode) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();
		List<AreaVO> areas;

		String query = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO "
				+ " FROM TCS_BEN_AREA WHERE CODIGO = :codigo ";
		namedParameters.put("codigo", strCode);

		areas = getNamedParameterJdbcTemplate().query(query.toString(),
				namedParameters, new RowMapper<AreaVO>() {
					public AreaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AreaVO areaVO = new AreaVO();
						areaVO.setCode(rs.getLong("CODIGO"));
						areaVO.setCodearea(rs.getString("AREA_CODIGO"));
						areaVO.setDescripcion(rs.getString("DESCRIPCION"));
						areaVO.setNombre(rs.getString("NOMBRE"));
						return areaVO;
					}
				});
		return areas;
	}

	public int insertArea(String[] Arr) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = "INSERT INTO TCS_BEN_AREA("
				+ " CODIGO, NOMBRE, DESCRIPCION, AREA_CODIGO, VIGENCIA, usuario_crea, fecha_crea, terminal_crea, "
				+ " usuario_modifica, fecha_modifica, terminal_modifica) "
				+ " VALUES(SCS_BEN_AREA.NextVal, upper(:nombre), upper(:descripcion), :area_codigo, 'A', "
				+ " :usuario_crea, TO_DATE(:fecha_crea,'dd/mm/yyyy'), :terminal_crea, "
				+ " :usuario_modifica, TO_DATE(:fecha_modifica,'dd/mm/yyyy'), :terminal_modifica)";

		namedParameters.put("nombre", Arr[0]);// strNombre
		namedParameters.put("descripcion", Arr[1]);// strDescripcion
		namedParameters.put("area_codigo", Arr[2]);// strsubarea
		namedParameters.put("usuario_crea", Arr[3]);// user.getName()
		namedParameters.put("fecha_crea", Arr[4]);// dateFormat.format(c.getTime())
		namedParameters.put("terminal_crea", Arr[5]);// strTerminal
		namedParameters.put("usuario_modifica", Arr[6]);// user.getName()
		namedParameters.put("fecha_modifica", Arr[7]);// dateFormat.format(c.getTime())
		namedParameters.put("terminal_modifica", Arr[8]);// strTerminal

		return getNamedParameterJdbcTemplate().update(query.toString(),
				namedParameters);
	}

	public int updateArea(String[] Arr, String strCode) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = "UPDATE TCS_BEN_AREA SET NOMBRE = UPPER(:nombre), "
				+ " DESCRIPCION = UPPER(:descripcion), usuario_modifica = :usuario_modifica, "
				+ " fecha_modifica = TO_DATE(:fecha_modifica,'dd/mm/yyyy'), "
				+ " terminal_modifica = :terminal_modifica WHERE CODIGO = :codigo";

		namedParameters.put("nombre", Arr[0]);// strNombre
		namedParameters.put("descripcion", Arr[1]);// srtDescripcion
		namedParameters.put("usuario_modifica", Arr[2]);// user.getName()
		namedParameters.put("fecha_modifica", Arr[3]);// dateFormat.format(c.getTime())
		namedParameters.put("terminal_modifica", Arr[4]);// strTerminal
		namedParameters.put("codigo", strCode);
		return getNamedParameterJdbcTemplate().update(query.toString(),
				namedParameters);
	}

	public int updateSubArea(String[] Arr, String strCode) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = " UPDATE TCS_BEN_AREA SET NOMBRE = UPPER(:nombre), "
				+ " DESCRIPCION = UPPER(:descripcion), AREA_CODIGO = :area_codigo, "
				+ " usuario_modifica = :usuario_modifica, fecha_modifica = TO_DATE(:fecha_modifica,'dd/mm/yyyy'), "
				+ " terminal_modifica = :terminal_modifica WHERE CODIGO = :codigo ";

		namedParameters.put("nombre", Arr[0]);// strNombre
		namedParameters.put("descripcion", Arr[1]);// strDescripcion
		namedParameters.put("area_codigo", Arr[2]);// strSubarea
		namedParameters.put("usuario_modifica", Arr[3]);// user.getName()
		namedParameters.put("fecha_modifica", Arr[4]);// dateFormat.format(c.getTime())
		namedParameters.put("terminal_modifica", Arr[5]);// strTerminal
		namedParameters.put("codigo", strCode);

		return getNamedParameterJdbcTemplate().update(query.toString(),
				namedParameters);
	}

	public Collection<AreaVO> findAllSubAreas(String strCodeArea) {
		List<AreaVO> areas;
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = "SELECT CODIGO, NOMBRE, DESCRIPCION, NVL(AREA_CODIGO,'') AREA_CODIGO, "
				+ " (SELECT NOMBRE FROM TCS_BEN_AREA WHERE CODIGO = :codigo_subconsulta ) AREA "
				+ " FROM TCS_BEN_AREA "
				+ " WHERE AREA_CODIGO = :codigo "
				+ " ORDER BY NOMBRE";
		namedParameters.put("codigo_subconsulta", strCodeArea);
		namedParameters.put("codigo", strCodeArea);

		areas = getNamedParameterJdbcTemplate().query(query.toString(),
				namedParameters, new RowMapper<AreaVO>() {
					public AreaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AreaVO areaVO = new AreaVO();
						areaVO.setCodearea(rs.getString("AREA_CODIGO"));
						areaVO.setDescripcion(rs.getString("DESCRIPCION"));
						areaVO.setNombre(rs.getString("AREA"));
						areaVO.setNombresubarea(rs.getString("NOMBRE"));
						areaVO.setCode(new Long(rs.getString("CODIGO")));
						return areaVO;
					}
				});
		return areas;
	}

	public Collection<AreaVO> findAreasAuthorized(String strCodeUsu,
			String strGroupUser) {
		List<AreaVO> secretarias;
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = null;
		if (strGroupUser.equals("ADMINISTRADOR GENERAL")
				|| strGroupUser.equals("SUPER USUARIO")) {
			query = "SELECT distinct area.CODIGO codigo, area.NOMBRE nombre "
					+ " FROM tcs_ben_area area "
					+ " WHERE area.AREA_CODIGO IS NULL "
					+ " ORDER BY area.NOMBRE ";
		} else {
			query = "SELECT a.codigo codigo, a.nombre nombre "
					+ " FROM tcs_ben_area a "
					+ " WHERE a.codigo = ANY(SELECT pro.area_codigo "
					+ " FROM TCS_BEN_PROGRAMA pro "
					+ " WHERE (pro.vigencia = 'A' OR sysdate BETWEEN pro.DESDE_VIGENCIA AND pro.HASTA_VIGENCIA)  "
					+ " AND pro.usu_codigo = :pro.usu_codigo "
					+ " UNION select area.area_codigo "
					+ " FROM tcs_ben_subprograma sub, tcs_ben_area area "
					+ " WHERE sub.area_codigo = area.codigo "
					+ " AND (sub.vigencia = 'A' OR sysdate between sub.DESDE_VIGENCIA and  sub.HASTA_VIGENCIA) "
					+ " AND sub.usu_codigo = :sub.usu_codigo "
					+ " UNION SELECT campo_n1 "
					+ " FROM paoyer.tcs_usuario "
					+ " WHERE campo_n1 IS NOT NULL "
					+ " AND codigo = :codigo "
					+ " UNION  SELECT area.area_codigo "
					+ " FROM tcs_ben_subprograma_usuario subus, tcs_ben_subprograma sub, tcs_ben_area area "
					+ " WHERE subus.SBPR_CODIGO = sub.CODIGO "
					+ " AND sub.area_codigo = area.codigo "
					+ " AND (sub.vigencia = 'A' OR sysdate BETWEEN sub.DESDE_VIGENCIA AND sub.HASTA_VIGENCIA) "
					+ " AND subus.USU_CODIGO = :subus.USU_CODIGO ) ORDER BY a.NOMBRE ";
			namedParameters.put("pro.usu_codigo", strCodeUsu);
			namedParameters.put("sub.usu_codigo", strCodeUsu);
			namedParameters.put("codigo", strCodeUsu);
			namedParameters.put("subus.USU_CODIGO", strCodeUsu);
		}

		secretarias = getNamedParameterJdbcTemplate().query(query.toString(),
				namedParameters, new RowMapper<AreaVO>() {
					public AreaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AreaVO areaVO = new AreaVO();
						areaVO.setCodearea(rs.getString("codigo"));
						areaVO.setNombre(rs.getString("nombre"));
						return areaVO;
					}
				});
		return secretarias;
	}

	public Collection<AreaVO> findAreasAuthorizedWithGestor(String strCodeUsu,
			String strGroupUser) {
		List<AreaVO> areas;
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = null;
		if (strGroupUser.equals("ADMINISTRADOR GENERAL")
				|| strGroupUser.equals("SUPER USUARIO")) {
			query = "SELECT distinct sec.codigo, sec.nombre FROM "
					+ " tcs_ben_area sec, tcs_ben_area area, "
					+ " tcs_ben_subprograma sub, "
					+ " tcs_ben_subp_atr_beneficiario sab WHERE "
					+ " sec.codigo = area.area_codigo "
					+ " AND area.codigo = sub.area_codigo "
					+ " AND sub.codigo = sab.sbpr_codigo "
					+ " AND UPPER(TRIM(sab.nombre)) = 'GESTOR' "
					+ " ORDER BY nombre ";
		} else {
			query = "SELECT sec.codigo codigo, sec.nombre nombre "
					+ " FROM tcs_ben_area sec WHERE sec.codigo IN ( "
					+ "	SELECT area.area_codigo FROM "
					+ "			tcs_ben_area area, "
					+ "			tcs_ben_subp_atr_beneficiario sab, "
					+ "			tcs_ben_subprograma sub, "
					+ "			tcs_ben_subprograma_usuario subus WHERE "
					+ "			area.codigo = sub.area_codigo "
					+ "			AND upper(trim(sab.nombre)) = 'GESTOR' "
					+ "			AND sab.sbpr_codigo = sub.codigo "
					+ "			AND sub.codigo = subus.sbpr_codigo "
					+ "			AND subus.usu_codigo = :subus.usu_codigo UNION "
					+ "	SELECT subus.campo_n1 FROM "
					+ "			tcs_ben_subp_atr_beneficiario sab, "
					+ "			tcs_ben_subprograma sub, " + "			tcs_ben_area area, "
					+ "			paoyer.tcs_usuario subus WHERE "
					+ "			upper(trim(sab.nombre)) = 'GESTOR' "
					+ "			AND sab.sbpr_codigo = sub.codigo "
					+ "			AND sub.area_codigo = area.codigo "
					+ "			AND area.area_codigo = subus.campo_n1 "
					+ "			AND subus.codigo = :subus.codigo )"
					+ "order by sec.nombre";
			namedParameters.put("subus.usu_codigo", strCodeUsu);
			namedParameters.put("subus.codigo", strCodeUsu);
		}

		areas = getNamedParameterJdbcTemplate().query(query.toString(),
				namedParameters, new RowMapper<AreaVO>() {
					public AreaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AreaVO areaVO = new AreaVO();
						areaVO.setCodearea(rs.getString("CODIGO"));
						areaVO.setNombre(rs.getString("NOMBRE"));
						return areaVO;
					}
				});
		return areas;
	}

	public AreaVO findAreaByProg(String strCodeProg) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = "SELECT area.CODIGO CODEAREA, area.NOMBRE NAMEAREA "
				+ " FROM tcs_ben_programa prog, tcs_ben_area area "
				+ " WHERE prog.AREA_CODIGO = area.CODIGO "
				+ " AND prog.codigo = :codigo ";

		namedParameters.put("codigo", strCodeProg);
		return getNamedParameterJdbcTemplate().queryForObject(query.toString(),
				namedParameters, new RowMapper<AreaVO>() {
					public AreaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AreaVO areaVO = new AreaVO();
						areaVO.setNombre(rs.getString("NAMEAREA"));
						areaVO.setCode(rs.getLong("CODEAREA"));
						return areaVO;
					}
				});
	}

}
